package a_aa_amainpackage.a_o2odata_deal.utils

import a_aa_amainpackage.a_o2odata_deal.config.config._
import org.apache.spark.sql.{DataFrame, SparkSession}
/*
*项目名: o2odata_commend
*文件名: cate_join
*创建者: hancongcong
*创建时间:2019/8/20 11:38
*描述: TODO
*/ object cateaddressjoin {



  def cate_restart(spark:SparkSession,conandvar_res:DataFrame):DataFrame  ={
    //关联分类
    val cate_res = catedata(spark,conandvar_res)
    //农产品关联
    val cate_resdata = cate_pro(spark,cate_res)
    //地址关联
    val address_res = addrssjoin(spark,cate_resdata)
    //商务部农产品关联
    val swb_products_res = swb_productsjoin(spark,address_res)
    return swb_products_res
  }

  /**
    * 关联分类
    * @param spark
    * @return
    */
  def catedata(spark:SparkSession,conandvar_res:DataFrame):DataFrame={
    //提取有问题的分类关联
    val casedata = conandvar_res.where("categoryId  = '50010535' or  categoryId  = '123256002' or categoryId  = '50008059' or categoryId  = '123210006'" +
      "or categoryId  = '121422025' or categoryId  = '50008056' or categoryId  = '50016092' or categoryId  = '124302002'" +
      "or categoryId  = '124312003' or categoryId  = '124320001' or categoryId  = '124456021' or categoryId  = '124466010'" +
      "or categoryId  = '124468007' or categoryId  = '124478011' or categoryId  = '124492008' or categoryId  = '124512008'" +
      "or categoryId  = '126474004' or categoryId  = '50008055' or categoryId  = '124292002' or categoryId  = '124296001'" +
      "or categoryId  = '124300001' or categoryId  = '124302001' or categoryId  = '124304001' or categoryId  = '124306001'" +
      "or categoryId  = '124308001' or categoryId  = '124310001' or categoryId  = '124312001' or categoryId  = '124604001'" +
      "or categoryId  = '124606001' or categoryId  = '124606002' or categoryId  = '50008062' or categoryId  = '50010513'" +
      "or categoryId  = '50012598' or categoryId  = '50010511' or categoryId  = '124298001' or categoryId  = '1101' or  categoryId  = '124048001'  " +
      "or categoryId  = '124252007' or categoryId  = '50008554' or categoryId  = '350401' or categoryId  = '50024944' or categoryId  = '50023280' or categoryId  = '1205'" +
      "or categoryId  = '50008882' or categoryId  = '50348001' or categoryId  = '50007006' or categoryId  = '121468012' or  categoryId  = '121458012' " +
      "or categoryId  = '50011991' or categoryId  = '121410013' or categoryId  = '50006015' or categoryId  = '50012392' or  categoryId  = '50012391' " +
      "or categoryId  = '50016427' or categoryId  = '50014781' or categoryId  = '50003558' or categoryId  = '50020032' or  categoryId  = '50015564' " +
      "or categoryId  = '50013976' or categoryId  = '127862002'").registerTempTable("casedata")
    val tmcatedf1 = spark.sql(
      s"""
         |select
         |*,
         |case
         |--饼干糕点
         |when (categoryId = '50010535' or categoryId = '123256002' or categoryId = '50008059' or categoryId = '123210006') then
         |(case
         |when categoryId='50010535' and  (title rlike '坚果大礼包' or title rlike '坚果组合' or title rlike '坚果炒货' or title rlike '坚果礼盒' or title rlike '每日坚果'
         |or title rlike '坚果零食大礼包' or title rlike '坚果混合' or title rlike '混合坚果' or title rlike '坚果干果混合') then 'jg_50010535'
         |when categoryId='123256002' and  (title rlike '坚果大礼包' or title rlike '坚果组合' or title rlike '坚果炒货' or title rlike '坚果礼盒' or title rlike '每日坚果'
         |or title rlike '坚果零食大礼包' or title rlike '坚果混合' or title rlike '混合坚果' or title rlike '坚果干果混合') then 'jg_123256002'
         |when categoryId='50008059' and  (title rlike '坚果大礼包' or title rlike '坚果组合' or title rlike '坚果炒货' or title rlike '坚果礼盒' or title rlike '每日坚果'
         |or title rlike '坚果零食大礼包' or title rlike '坚果混合' or title rlike '混合坚果' or title rlike '坚果干果混合') then 'jg_50008059'
         |when categoryId='123210006' and  (title rlike '坚果大礼包' or title rlike '坚果组合' or title rlike '坚果炒货' or title rlike '坚果礼盒' or title rlike '每日坚果'
         |or title rlike '坚果零食大礼包' or title rlike '坚果混合' or title rlike '混合坚果' or title rlike '坚果干果混合') then 'jg_123210006'
         |
         |when categoryId='50010535' and (title rlike '混装' or title rlike '组合整箱') then categoryId
         |when categoryId='123256002' and (title rlike '混装' or title rlike '组合整箱') then categoryId
         |when categoryId='50008059' and (title rlike '混装' or title rlike '组合整箱') then categoryId
         |when categoryId='123210006' and (title rlike '混装' or title rlike '组合整箱') then categoryId
         |
         |when categoryId='50010535' and (title rlike '薯片' or title rlike '爆米花' or title rlike '山药薄片' or title rlike '虾片' or title rlike '虾条'
         |or title rlike '雪米饼' or title  rlike '锅巴' or title rlike '烤香馍片' or title rlike '拉面丸子' or title rlike '仙贝') then 'sp_50010535'
         |when categoryId='123256002' and (title rlike '薯片' or title rlike '爆米花' or title rlike '山药薄片' or title rlike '虾片' or title rlike '虾条'
         |or title rlike '雪米饼' or title rlike '锅巴' or title rlike '烤香馍片' or title rlike '拉面丸子' or title rlike '仙贝') then 'sp_123256002'
         |when categoryId='50008059' and (title rlike '薯片' or title rlike '爆米花' or title rlike '山药薄片' or title rlike '虾片' or title rlike '虾条'
         |or title rlike '雪米饼' or title rlike '锅巴' or title rlike '烤香馍片' or title rlike '拉面丸子' or title rlike '仙贝') then 'sp_50008059'
         |when categoryId='123210006' and (title rlike '薯片' or title rlike '爆米花' or title rlike '山药薄片' or title rlike '虾片' or title rlike '虾条'
         |or title rlike '雪米饼' or title rlike '锅巴' or title rlike '烤香馍片' or title rlike '拉面丸子' or title rlike '仙贝') then 'sp_123210006'
         |
         |when categoryId='50010535' and (title rlike '威化饼干' or title rlike '手指饼干' or title rlike '奶酪饼干' or title rlike '糙米卷' or title rlike '饼干糕点'
         |or title rlike '香葱饼干' or title rlike '字母饼干' or title rlike '饼干碎屑' or title rlike '薄脆饼干' or title rlike '夹心饼干') then 'wh_50010535'
         |when categoryId='123256002' and (title rlike '威化饼干' or title rlike '手指饼干' or title rlike '奶酪饼干' or title rlike '糙米卷' or title rlike '饼干糕点'
         |or title rlike '香葱饼干' or title rlike '字母饼干' or title rlike '饼干碎屑' or title rlike '薄脆饼干' or title rlike '夹心饼干') then 'wh_123256002'
         |when categoryId='50008059' and (title rlike '威化饼干' or title rlike '手指饼干' or title rlike '奶酪饼干' or title rlike '糙米卷' or title rlike '饼干糕点'
         |or title rlike '香葱饼干' or title rlike '字母饼干' or title rlike '饼干碎屑' or title rlike '薄脆饼干' or title rlike '夹心饼干') then 'wh_50008059'
         |when categoryId='123210006' and (title rlike '威化饼干' or title rlike '手指饼干' or title rlike '奶酪饼干' or title rlike '糙米卷' or title rlike '饼干糕点'
         |or title rlike '香葱饼干' or title rlike '字母饼干' or title rlike '饼干碎屑' or title rlike '薄脆饼干' or title rlike '夹心饼干') then 'wh_123210006'
         |
         |when categoryId='50010535' and  (title rlike '牛奶夹心巧克力' or title rlike '巧克力豆' or title rlike '星球杯' or title rlike '奇趣蛋' or title rlike '巧克力礼盒') then 'qkl_50010535'
         |when categoryId='123256002' and  (title rlike '牛奶夹心巧克力' or title rlike '巧克力豆' or title rlike '星球杯' or title rlike '奇趣蛋' or title rlike '巧克力礼盒') then 'qkl_123256002'
         |when categoryId='50008059' and  (title rlike '牛奶夹心巧克力' or title rlike '巧克力豆' or title rlike '星球杯' or title rlike '奇趣蛋' or title rlike '巧克力礼盒') then 'qkl_50008059'
         |when categoryId='123210006' and  (title rlike '牛奶夹心巧克力' or title rlike '巧克力豆' or title rlike '星球杯' or title rlike '奇趣蛋' or title rlike '巧克力礼盒') then 'qkl_123210006'
         |
         |when categoryId='50010535' and (title rlike '棒棒糖' or title rlike 'QQ糖' or (title rlike '水果' and title rlike '软糖') or title rlike '混合装糖果'
         |or title rlike '薄荷味糖' or title rlike '口香糖' or title rlike '水果糖' or title rlike '紫皮糖' or title rlike '玉米软糖' or title rlike '什锦糖' or title rlike '软心球' or title rlike '什锦喜庆糖' or title rlike '牛轧糖' or title rlike '酥心糖') then 'bbt_50010535'
         |when categoryId='123256002' and (title rlike '棒棒糖' or title rlike 'QQ糖' or (title rlike '水果' and title rlike '软糖') or title rlike '混合装糖果'
         |or title rlike '薄荷味糖' or title rlike '口香糖' or title rlike '水果糖' or title rlike '紫皮糖' or title rlike '玉米软糖' or title rlike '什锦糖' or title rlike '软心球' or title rlike '什锦喜庆糖' or title rlike '牛轧糖' or title rlike '酥心糖') then 'bbt_123256002'
         |when categoryId='50008059' and (title rlike '棒棒糖' or title rlike 'QQ糖' or (title rlike '水果' and title rlike '软糖') or title rlike '混合装糖果'
         |or title rlike '薄荷味糖' or title rlike '口香糖' or title rlike '水果糖' or title rlike '紫皮糖' or title rlike '玉米软糖' or title rlike '什锦糖' or title rlike '软心球' or title rlike '什锦喜庆糖' or title rlike '牛轧糖' or title rlike '酥心糖') then 'bbt_50008059'
         |when categoryId='123210006' and (title rlike '棒棒糖' or title rlike 'QQ糖' or (title rlike '水果' and title rlike '软糖') or title rlike '混合装糖果'
         |or title rlike '薄荷味糖' or title rlike '口香糖' or title rlike '水果糖' or title rlike '紫皮糖' or title rlike '玉米软糖' or title rlike '什锦糖' or title rlike '软心球' or title rlike '什锦喜庆糖' or title rlike '牛轧糖' or title rlike '酥心糖') then 'bbt_123210006'
         |else categoryId end
         |)
         |--笔记本
         |when (categoryId = '1101' or categoryId = '124048001') then
         |(case
         |when categoryId = '1101' and (title rlike 'GTX') then 'gtx_1101'
         |when categoryId = '124048001' and (title rlike 'GTX') then 'gtx_124048001'
         |when categoryId = '1101' and (title rlike '轻薄' or title rlike '超薄') then 'qb_1101'
         |when categoryId = '124048001' and (title rlike '轻薄' or title rlike '超薄') then 'qb_124048001'
         |else categoryId end
         |)
         |--巧克力
         |when (categoryId = '121422025' or categoryId = '50008056' or categoryId = '50016092') then
         |(case
         |when categoryId = '121422025' and (title rlike '原料' or title rlike '泡泡糖' or title rlike '口香糖') then categoryId
         |when categoryId = '50008056' and (title rlike '原料' or title rlike '泡泡糖' or title rlike '口香糖') then categoryId
         |when categoryId = '50016092' and (title rlike '原料' or title rlike '泡泡糖' or title rlike '口香糖') then categoryId
         |when categoryId = '121422025' and (title rlike '牛奶夹心巧克力' or title rlike '巧克力豆' or title rlike '星球杯' or title rlike '奇趣蛋'
         |or title rlike '巧克力礼盒') then 'qkl_121422025'
         |when categoryId = '50008056' and (title rlike '牛奶夹心巧克力' or title rlike '巧克力豆' or title rlike '星球杯' or title rlike '奇趣蛋'
         |or title rlike '巧克力礼盒') then 'qkl_50008056'
         |when categoryId = '50016092' and (title rlike '牛奶夹心巧克力' or title rlike '巧克力豆' or title rlike '星球杯' or title rlike '奇趣蛋'
         |or title rlike '巧克力礼盒') then 'qkl_50016092'
         |when categoryId = '121422025' and (title rlike '薯片' or title rlike '爆米花' or title rlike '虾片' or title rlike '虾条' or title rlike '雪米饼'
         |or title rlike '锅巴' or title rlike '烤香馍片' or title rlike '拉面丸子' or title rlike '仙贝')  then 'sp_121422025'
         |when categoryId = '50008056' and (title rlike '薯片' or title rlike '爆米花' or title rlike '虾片' or title rlike '虾条' or title rlike '雪米饼'
         |or title rlike '锅巴' or title rlike '烤香馍片' or title rlike '拉面丸子' or title rlike '仙贝') then 'sp_50008056'
         |when categoryId = '50016092' and (title rlike '薯片' or title rlike '爆米花' or title rlike '虾片' or title rlike '虾条' or title rlike '雪米饼'
         |or title rlike '锅巴' or title rlike '烤香馍片' or title rlike '拉面丸子' or title rlike '仙贝') then 'sp_50016092'
         |
         |when categoryId = '121422025' and (title rlike '手撕面包' or title rlike '小口袋面包' or title rlike '京八件' or title rlike '华夫饼' or title rlike '月饼'
         |or title rlike '糕点礼盒' or title rlike '曲奇饼干' or title rlike '蒸蛋糕' or title rlike '拿破仑蛋糕' or title rlike '凤梨酥' or title rlike '沙琪玛'
         |or title rlike '高纤粗粮饼' or title rlike '蛋卷' or title rlike '夹心饼干' or title rlike '饼干棒' or title rlike '核桃饼' or title rlike '巧克力饼干')  then 'mb_121422025'
         |when categoryId = '50008056' and (title rlike '手撕面包' or title rlike '小口袋面包' or title rlike '京八件' or title rlike '华夫饼' or title rlike '月饼'
         |or title rlike '糕点礼盒' or title rlike '曲奇饼干' or title rlike '蒸蛋糕' or title rlike '拿破仑蛋糕' or title rlike '凤梨酥' or title rlike '沙琪玛'
         |or title rlike '高纤粗粮饼' or title rlike '蛋卷' or title rlike '夹心饼干' or title rlike '饼干棒' or title rlike '核桃饼' or title rlike '巧克力饼干') then 'mb_50008056'
         |when categoryId = '50016092' and (title rlike '手撕面包' or title rlike '小口袋面包' or title rlike '京八件' or title rlike '华夫饼' or title rlike '月饼'
         |or title rlike '糕点礼盒' or title rlike '曲奇饼干' or title rlike '蒸蛋糕' or title rlike '拿破仑蛋糕' or title rlike '凤梨酥' or title rlike '沙琪玛'
         |or title rlike '高纤粗粮饼' or title rlike '蛋卷' or title rlike '夹心饼干' or title rlike '饼干棒' or title rlike '核桃饼' or title rlike '巧克力饼干') then 'mb_50016092'
         |else categoryId end
         |)
         |
         |--巧克力礼盒
         |when (categoryId = '124302002' or categoryId = '124312003' or categoryId = '124320001' or categoryId = '124456021' or categoryId = '124466010'
         |or categoryId = '124468007' or categoryId = '124478011' or categoryId = '124492008' or categoryId = '124512008' or categoryId = '126474004' or categoryId = '50008055') then
         |(case
         |when categoryId = '124302002' and (title rlike '坚果大礼包' or title rlike '每日坚果' or title rlike '混合坚果' or title rlike '坚果混合') then 'jg_124302002'
         |when categoryId = '124312003' and (title rlike '坚果大礼包' or title rlike '每日坚果' or title rlike '混合坚果' or title rlike '坚果混合') then 'jg_124312003'
         |when categoryId = '124320001' and (title rlike '坚果大礼包' or title rlike '每日坚果' or title rlike '混合坚果' or title rlike '坚果混合') then 'jg_124320001'
         |when categoryId = '124456021' and (title rlike '坚果大礼包' or title rlike '每日坚果' or title rlike '混合坚果' or title rlike '坚果混合') then 'jg_124456021'
         |when categoryId = '124466010' and (title rlike '坚果大礼包' or title rlike '每日坚果' or title rlike '混合坚果' or title rlike '坚果混合') then 'jg_124466010'
         |when categoryId = '124468007' and (title rlike '坚果大礼包' or title rlike '每日坚果' or title rlike '混合坚果' or title rlike '坚果混合') then 'jg_124468007'
         |when categoryId = '124478011' and (title rlike '坚果大礼包' or title rlike '每日坚果' or title rlike '混合坚果' or title rlike '坚果混合') then 'jg_124478011'
         |when categoryId = '124492008' and (title rlike '坚果大礼包' or title rlike '每日坚果' or title rlike '混合坚果' or title rlike '坚果混合') then 'jg_124492008'
         |when categoryId = '124512008' and (title rlike '坚果大礼包' or title rlike '每日坚果' or title rlike '混合坚果' or title rlike '坚果混合') then 'jg_124512008'
         |when categoryId = '126474004' and (title rlike '坚果大礼包' or title rlike '每日坚果' or title rlike '混合坚果' or title rlike '坚果混合') then 'jg_126474004'
         |when categoryId = '50008055' and (title rlike '坚果大礼包' or title rlike '每日坚果' or title rlike '混合坚果' or title rlike '坚果混合') then 'jg_50008055'
         |
         |when categoryId = '124302002' and (title rlike '薯片' or title rlike '爆米花' or title rlike '虾片' or title rlike '虾条' or title rlike '雪米饼'
         |or title rlike '锅巴' or title rlike '烤香馍片' or title rlike '拉面丸子' or title rlike '仙贝') then 'sp_124302002'
         |when categoryId = '124312003' and (title rlike '薯片' or title rlike '爆米花' or title rlike '虾片' or title rlike '虾条' or title rlike '雪米饼'
         |or title rlike '锅巴' or title rlike '烤香馍片' or title rlike '拉面丸子' or title rlike '仙贝') then 'sp_124312003'
         |when categoryId = '124320001' and (title rlike '薯片' or title rlike '爆米花' or title rlike '虾片' or title rlike '虾条' or title rlike '雪米饼'
         |or title rlike '锅巴' or title rlike '烤香馍片' or title rlike '拉面丸子' or title rlike '仙贝') then 'sp_124320001'
         |when categoryId = '124456021' and (title rlike '薯片' or title rlike '爆米花' or title rlike '虾片' or title rlike '虾条' or title rlike '雪米饼'
         |or title rlike '锅巴' or title rlike '烤香馍片' or title rlike '拉面丸子' or title rlike '仙贝') then 'sp_124456021'
         |when categoryId = '124466010' and (title rlike '薯片' or title rlike '爆米花' or title rlike '虾片' or title rlike '虾条' or title rlike '雪米饼'
         |or title rlike '锅巴' or title rlike '烤香馍片' or title rlike '拉面丸子' or title rlike '仙贝') then 'sp_124466010'
         |when categoryId = '124468007' and (title rlike '薯片' or title rlike '爆米花' or title rlike '虾片' or title rlike '虾条' or title rlike '雪米饼'
         |or title rlike '锅巴' or title rlike '烤香馍片' or title rlike '拉面丸子' or title rlike '仙贝') then 'sp_124468007'
         |when categoryId = '124478011' and (title rlike '薯片' or title rlike '爆米花' or title rlike '虾片' or title rlike '虾条' or title rlike '雪米饼'
         |or title rlike '锅巴' or title rlike '烤香馍片' or title rlike '拉面丸子' or title rlike '仙贝') then 'sp_124478011'
         |when categoryId = '124492008' and (title rlike '薯片' or title rlike '爆米花' or title rlike '虾片' or title rlike '虾条' or title rlike '雪米饼'
         |or title rlike '锅巴' or title rlike '烤香馍片' or title rlike '拉面丸子' or title rlike '仙贝') then 'sp_124492008'
         |when categoryId = '124512008' and (title rlike '薯片' or title rlike '爆米花' or title rlike '虾片' or title rlike '虾条' or title rlike '雪米饼'
         |or title rlike '锅巴' or title rlike '烤香馍片' or title rlike '拉面丸子' or title rlike '仙贝') then 'sp_124512008'
         |when categoryId = '126474004' and (title rlike '薯片' or title rlike '爆米花' or title rlike '虾片' or title rlike '虾条' or title rlike '雪米饼'
         |or title rlike '锅巴' or title rlike '烤香馍片' or title rlike '拉面丸子' or title rlike '仙贝') then 'sp_126474004'
         |when categoryId = '50008055' and (title rlike '薯片' or title rlike '爆米花' or title rlike '虾片' or title rlike '虾条' or title rlike '雪米饼'
         |or title rlike '锅巴' or title rlike '烤香馍片' or title rlike '拉面丸子' or title rlike '仙贝') then 'sp_50008055'
         |
         |when categoryId = '124302002' and (title rlike '手撕面包' or title rlike '小口袋面包' or title rlike '京八件' or title rlike '华夫饼' or title rlike '月饼'
         |or title rlike '糕点礼盒' or title rlike '曲奇饼干' or title rlike '蒸蛋糕' or title rlike '拿破仑蛋糕' or title rlike '凤梨酥' or title rlike '沙琪玛' or title rlike '高纤粗粮饼'
         |or title rlike '蛋卷' or title rlike '夹心饼干' or title rlike '核桃饼' or title rlike '巧克力饼干') then 'mb_124302002'
         |when categoryId = '124312003' and (title rlike '手撕面包' or title rlike '小口袋面包' or title rlike '京八件' or title rlike '华夫饼' or title rlike '月饼'
         |or title rlike '糕点礼盒' or title rlike '曲奇饼干' or title rlike '蒸蛋糕' or title rlike '拿破仑蛋糕' or title rlike '凤梨酥' or title rlike '沙琪玛' or title rlike '高纤粗粮饼'
         |or title rlike '蛋卷' or title rlike '夹心饼干' or title rlike '核桃饼' or title rlike '巧克力饼干') then 'mb_124312003'
         |when categoryId = '124320001' and (title rlike '手撕面包' or title rlike '小口袋面包' or title rlike '京八件' or title rlike '华夫饼' or title rlike '月饼'
         |or title rlike '糕点礼盒' or title rlike '曲奇饼干' or title rlike '蒸蛋糕' or title rlike '拿破仑蛋糕' or title rlike '凤梨酥' or title rlike '沙琪玛' or title rlike '高纤粗粮饼'
         |or title rlike '蛋卷' or title rlike '夹心饼干' or title rlike '核桃饼' or title rlike '巧克力饼干') then 'mb_124320001'
         |when categoryId = '124456021' and (title rlike '手撕面包' or title rlike '小口袋面包' or title rlike '京八件' or title rlike '华夫饼' or title rlike '月饼'
         |or title rlike '糕点礼盒' or title rlike '曲奇饼干' or title rlike '蒸蛋糕' or title rlike '拿破仑蛋糕' or title rlike '凤梨酥' or title rlike '沙琪玛' or title rlike '高纤粗粮饼'
         |or title rlike '蛋卷' or title rlike '夹心饼干' or title rlike '核桃饼' or title rlike '巧克力饼干') then 'mb_124456021'
         |when categoryId = '124466010' and (title rlike '手撕面包' or title rlike '小口袋面包' or title rlike '京八件' or title rlike '华夫饼' or title rlike '月饼'
         |or title rlike '糕点礼盒' or title rlike '曲奇饼干' or title rlike '蒸蛋糕' or title rlike '拿破仑蛋糕' or title rlike '凤梨酥' or title rlike '沙琪玛' or title rlike '高纤粗粮饼'
         |or title rlike '蛋卷' or title rlike '夹心饼干' or title rlike '核桃饼' or title rlike '巧克力饼干') then 'mb_124466010'
         |when categoryId = '124468007' and (title rlike '手撕面包' or title rlike '小口袋面包' or title rlike '京八件' or title rlike '华夫饼' or title rlike '月饼'
         |or title rlike '糕点礼盒' or title rlike '曲奇饼干' or title rlike '蒸蛋糕' or title rlike '拿破仑蛋糕' or title rlike '凤梨酥' or title rlike '沙琪玛' or title rlike '高纤粗粮饼'
         |or title rlike '蛋卷' or title rlike '夹心饼干' or title rlike '核桃饼' or title rlike '巧克力饼干') then 'mb_124468007'
         |when categoryId = '124478011' and (title rlike '手撕面包' or title rlike '小口袋面包' or title rlike '京八件' or title rlike '华夫饼' or title rlike '月饼'
         |or title rlike '糕点礼盒' or title rlike '曲奇饼干' or title rlike '蒸蛋糕' or title rlike '拿破仑蛋糕' or title rlike '凤梨酥' or title rlike '沙琪玛' or title rlike '高纤粗粮饼'
         |or title rlike '蛋卷' or title rlike '夹心饼干' or title rlike '核桃饼' or title rlike '巧克力饼干') then 'mb_124478011'
         |when categoryId = '124492008' and (title rlike '手撕面包' or title rlike '小口袋面包' or title rlike '京八件' or title rlike '华夫饼' or title rlike '月饼'
         |or title rlike '糕点礼盒' or title rlike '曲奇饼干' or title rlike '蒸蛋糕' or title rlike '拿破仑蛋糕' or title rlike '凤梨酥' or title rlike '沙琪玛' or title rlike '高纤粗粮饼'
         |or title rlike '蛋卷' or title rlike '夹心饼干' or title rlike '核桃饼' or title rlike '巧克力饼干') then 'mb_124492008'
         |when categoryId = '124512008' and (title rlike '手撕面包' or title rlike '小口袋面包' or title rlike '京八件' or title rlike '华夫饼' or title rlike '月饼'
         |or title rlike '糕点礼盒' or title rlike '曲奇饼干' or title rlike '蒸蛋糕' or title rlike '拿破仑蛋糕' or title rlike '凤梨酥' or title rlike '沙琪玛' or title rlike '高纤粗粮饼'
         |or title rlike '蛋卷' or title rlike '夹心饼干' or title rlike '核桃饼' or title rlike '巧克力饼干') then 'mb_124512008'
         |when categoryId = '126474004' and (title rlike '手撕面包' or title rlike '小口袋面包' or title rlike '京八件' or title rlike '华夫饼' or title rlike '月饼'
         |or title rlike '糕点礼盒' or title rlike '曲奇饼干' or title rlike '蒸蛋糕' or title rlike '拿破仑蛋糕' or title rlike '凤梨酥' or title rlike '沙琪玛' or title rlike '高纤粗粮饼'
         |or title rlike '蛋卷' or title rlike '夹心饼干' or title rlike '核桃饼' or title rlike '巧克力饼干') then 'mb_126474004'
         |when categoryId = '50008055' and (title rlike '手撕面包' or title rlike '小口袋面包' or title rlike '京八件' or title rlike '华夫饼' or title rlike '月饼'
         |or title rlike '糕点礼盒' or title rlike '曲奇饼干' or title rlike '蒸蛋糕' or title rlike '拿破仑蛋糕' or title rlike '凤梨酥' or title rlike '沙琪玛' or title rlike '高纤粗粮饼'
         |or title rlike '蛋卷' or title rlike '夹心饼干' or title rlike '核桃饼' or title rlike '巧克力饼干') then 'mb_50008055'
         |
         |when categoryId = '124302002' and (title rlike '棒棒糖' or title rlike 'QQ糖' or (title rlike '水果' and title rlike '软糖') or title rlike '混合装糖果'
         |or title rlike '薄荷味糖' or title rlike '口香糖' or title rlike '水果糖' or title rlike '紫皮糖' or title rlike '玉米软糖' or title rlike '什锦糖' or title rlike '软心球'
         |or title rlike '什锦喜庆糖' or title rlike '牛轧糖' or title rlike '酥心糖' ) then 'bbt_124302002'
         |when categoryId = '124312003' and (title rlike '棒棒糖' or title rlike 'QQ糖' or (title rlike '水果' and title rlike '软糖') or title rlike '混合装糖果'
         |or title rlike '薄荷味糖' or title rlike '口香糖' or title rlike '水果糖' or title rlike '紫皮糖' or title rlike '玉米软糖' or title rlike '什锦糖' or title rlike '软心球'
         |or title rlike '什锦喜庆糖' or title rlike '牛轧糖' or title rlike '酥心糖') then 'bbt_124312003'
         |when categoryId = '124320001' and (title rlike '棒棒糖' or title rlike 'QQ糖' or (title rlike '水果' and title rlike '软糖') or title rlike '混合装糖果'
         |or title rlike '薄荷味糖' or title rlike '口香糖' or title rlike '水果糖' or title rlike '紫皮糖' or title rlike '玉米软糖' or title rlike '什锦糖' or title rlike '软心球'
         |or title rlike '什锦喜庆糖' or title rlike '牛轧糖' or title rlike '酥心糖') then 'bbt_124320001'
         |when categoryId = '124456021' and (title rlike '棒棒糖' or title rlike 'QQ糖' or (title rlike '水果' and title rlike '软糖') or title rlike '混合装糖果'
         |or title rlike '薄荷味糖' or title rlike '口香糖' or title rlike '水果糖' or title rlike '紫皮糖' or title rlike '玉米软糖' or title rlike '什锦糖' or title rlike '软心球'
         |or title rlike '什锦喜庆糖' or title rlike '牛轧糖' or title rlike '酥心糖') then 'bbt_124456021'
         |when categoryId = '124466010' and (title rlike '棒棒糖' or title rlike 'QQ糖' or (title rlike '水果' and title rlike '软糖') or title rlike '混合装糖果'
         |or title rlike '薄荷味糖' or title rlike '口香糖' or title rlike '水果糖' or title rlike '紫皮糖' or title rlike '玉米软糖' or title rlike '什锦糖' or title rlike '软心球'
         |or title rlike '什锦喜庆糖' or title rlike '牛轧糖' or title rlike '酥心糖') then 'bbt_124466010'
         |when categoryId = '124468007' and (title rlike '棒棒糖' or title rlike 'QQ糖' or (title rlike '水果' and title rlike '软糖') or title rlike '混合装糖果'
         |or title rlike '薄荷味糖' or title rlike '口香糖' or title rlike '水果糖' or title rlike '紫皮糖' or title rlike '玉米软糖' or title rlike '什锦糖' or title rlike '软心球'
         |or title rlike '什锦喜庆糖' or title rlike '牛轧糖' or title rlike '酥心糖') then 'bbt_124468007'
         |when categoryId = '124478011' and (title rlike '棒棒糖' or title rlike 'QQ糖' or (title rlike '水果' and title rlike '软糖') or title rlike '混合装糖果'
         |or title rlike '薄荷味糖' or title rlike '口香糖' or title rlike '水果糖' or title rlike '紫皮糖' or title rlike '玉米软糖' or title rlike '什锦糖' or title rlike '软心球'
         |or title rlike '什锦喜庆糖' or title rlike '牛轧糖' or title rlike '酥心糖') then 'bbt_124478011'
         |when categoryId = '124492008' and (title rlike '棒棒糖' or title rlike 'QQ糖' or (title rlike '水果' and title rlike '软糖') or title rlike '混合装糖果'
         |or title rlike '薄荷味糖' or title rlike '口香糖' or title rlike '水果糖' or title rlike '紫皮糖' or title rlike '玉米软糖' or title rlike '什锦糖' or title rlike '软心球'
         |or title rlike '什锦喜庆糖' or title rlike '牛轧糖' or title rlike '酥心糖') then 'bbt_124492008'
         |when categoryId = '124512008' and (title rlike '棒棒糖' or title rlike 'QQ糖' or (title rlike '水果' and title rlike '软糖') or title rlike '混合装糖果'
         |or title rlike '薄荷味糖' or title rlike '口香糖' or title rlike '水果糖' or title rlike '紫皮糖' or title rlike '玉米软糖' or title rlike '什锦糖' or title rlike '软心球'
         |or title rlike '什锦喜庆糖' or title rlike '牛轧糖' or title rlike '酥心糖') then 'bbt_124512008'
         |when categoryId = '126474004' and (title rlike '棒棒糖' or title rlike 'QQ糖' or (title rlike '水果' and title rlike '软糖') or title rlike '混合装糖果'
         |or title rlike '薄荷味糖' or title rlike '口香糖' or title rlike '水果糖' or title rlike '紫皮糖' or title rlike '玉米软糖' or title rlike '什锦糖' or title rlike '软心球'
         |or title rlike '什锦喜庆糖' or title rlike '牛轧糖' or title rlike '酥心糖') then 'bbt_126474004'
         |when categoryId = '50008055' and (title rlike '棒棒糖' or title rlike 'QQ糖' or (title rlike '水果' and title rlike '软糖') or title rlike '混合装糖果'
         |or title rlike '薄荷味糖' or title rlike '口香糖' or title rlike '水果糖' or title rlike '紫皮糖' or title rlike '玉米软糖' or title rlike '什锦糖' or title rlike '软心球'
         |or title rlike '什锦喜庆糖' or title rlike '牛轧糖' or title rlike '酥心糖') then 'bbt_50008055'
         |else categoryId end
         |)
         |
         |--大礼包
         |when (categoryId = '124292002' or categoryId = '124296001' or categoryId = '124300001' or categoryId = '124302001' or categoryId = '124304001'
         |or categoryId = '124306001' or categoryId = '124308001' or categoryId = '124310001' or categoryId = '124312001' or categoryId = '124604001' or categoryId = '124606001'
         |or categoryId = '124606002' or categoryId = '50008062' or categoryId = '50010513' or categoryId = '50012598' or categoryId = '50010511' or categoryId = '124298001') then
         |(case
         |when categoryId = '124292002' and (title rlike '大礼包' or title rlike '混装') then   categoryId
         |when categoryId = '124296001' and (title rlike '大礼包' or title rlike '混装') then   categoryId
         |when categoryId = '124300001' and (title rlike '大礼包' or title rlike '混装') then   categoryId
         |when categoryId = '124302001' and (title rlike '大礼包' or title rlike '混装') then   categoryId
         |when categoryId = '124304001' and (title rlike '大礼包' or title rlike '混装') then   categoryId
         |when categoryId = '124306001' and (title rlike '大礼包' or title rlike '混装') then   categoryId
         |when categoryId = '124308001' and (title rlike '大礼包' or title rlike '混装') then   categoryId
         |when categoryId = '124310001' and (title rlike '大礼包' or title rlike '混装') then   categoryId
         |when categoryId = '124312001' and (title rlike '大礼包' or title rlike '混装') then   categoryId
         |when categoryId = '124604001' and (title rlike '大礼包' or title rlike '混装') then   categoryId
         |when categoryId = '124606001' and (title rlike '大礼包' or title rlike '混装') then   categoryId
         |when categoryId = '124606002' and (title rlike '大礼包' or title rlike '混装') then   categoryId
         |when categoryId = '50008062' and (title rlike '大礼包' or title rlike '混装') then  categoryId
         |when categoryId = '50010513' and (title rlike '大礼包' or title rlike '混装') then  categoryId
         |when categoryId = '50012598' and (title rlike '大礼包' or title rlike '混装') then  categoryId
         |when categoryId = '50010511' and (title rlike '大礼包' or title rlike '混装') then  categoryId
         |when categoryId = '124298001' and (title rlike '大礼包' or title rlike '混装') then categoryId
         |
         |when categoryId = '124292002' and (title rlike '薯片' or title rlike '爆米花' or title rlike '山药薄片' or title rlike '虾片' or title rlike '虾条'
         |or title rlike '雪米饼' or title rlike '锅巴' or title rlike '烤香馍片' or title rlike '拉面丸子'  or title rlike '仙贝') then 'sp_124292002'
         |when categoryId = '124296001' and (title rlike '薯片' or title rlike '爆米花' or title rlike '山药薄片' or title rlike '虾片' or title rlike '虾条'
         |or title rlike '雪米饼' or title rlike '锅巴' or title rlike '烤香馍片' or title rlike '拉面丸子'  or title rlike '仙贝') then 'sp_124296001'
         |when categoryId = '124300001' and (title rlike '薯片' or title rlike '爆米花' or title rlike '山药薄片' or title rlike '虾片' or title rlike '虾条'
         |or title rlike '雪米饼' or title rlike '锅巴' or title rlike '烤香馍片' or title rlike '拉面丸子'  or title rlike '仙贝') then 'sp_124300001'
         |when categoryId = '124302001' and (title rlike '薯片' or title rlike '爆米花' or title rlike '山药薄片' or title rlike '虾片' or title rlike '虾条'
         |or title rlike '雪米饼' or title rlike '锅巴' or title rlike '烤香馍片' or title rlike '拉面丸子'  or title rlike '仙贝') then 'sp_124302001'
         |when categoryId = '124304001' and (title rlike '薯片' or title rlike '爆米花' or title rlike '山药薄片' or title rlike '虾片' or title rlike '虾条'
         |or title rlike '雪米饼' or title rlike '锅巴' or title rlike '烤香馍片' or title rlike '拉面丸子'  or title rlike '仙贝') then 'sp_124304001'
         |when categoryId = '124306001' and (title rlike '薯片' or title rlike '爆米花' or title rlike '山药薄片' or title rlike '虾片' or title rlike '虾条'
         |or title rlike '雪米饼' or title rlike '锅巴' or title rlike '烤香馍片' or title rlike '拉面丸子'  or title rlike '仙贝') then 'sp_124306001'
         |when categoryId = '124308001' and (title rlike '薯片' or title rlike '爆米花' or title rlike '山药薄片' or title rlike '虾片' or title rlike '虾条'
         |or title rlike '雪米饼' or title rlike '锅巴' or title rlike '烤香馍片' or title rlike '拉面丸子'  or title rlike '仙贝') then 'sp_124308001'
         |when categoryId = '124310001' and (title rlike '薯片' or title rlike '爆米花' or title rlike '山药薄片' or title rlike '虾片' or title rlike '虾条'
         |or title rlike '雪米饼' or title rlike '锅巴' or title rlike '烤香馍片' or title rlike '拉面丸子'  or title rlike '仙贝') then 'sp_124310001'
         |when categoryId = '124312001' and (title rlike '薯片' or title rlike '爆米花' or title rlike '山药薄片' or title rlike '虾片' or title rlike '虾条'
         |or title rlike '雪米饼' or title rlike '锅巴' or title rlike '烤香馍片' or title rlike '拉面丸子'  or title rlike '仙贝') then 'sp_124312001'
         |when categoryId = '124604001' and (title rlike '薯片' or title rlike '爆米花' or title rlike '山药薄片' or title rlike '虾片' or title rlike '虾条'
         |or title rlike '雪米饼' or title rlike '锅巴' or title rlike '烤香馍片' or title rlike '拉面丸子'  or title rlike '仙贝') then 'sp_124604001'
         |when categoryId = '124606001' and (title rlike '薯片' or title rlike '爆米花' or title rlike '山药薄片' or title rlike '虾片' or title rlike '虾条'
         |or title rlike '雪米饼' or title rlike '锅巴' or title rlike '烤香馍片' or title rlike '拉面丸子'  or title rlike '仙贝') then 'sp_124606001'
         |when categoryId = '124606002' and (title rlike '薯片' or title rlike '爆米花' or title rlike '山药薄片' or title rlike '虾片' or title rlike '虾条'
         |or title rlike '雪米饼' or title rlike '锅巴' or title rlike '烤香馍片' or title rlike '拉面丸子'  or title rlike '仙贝') then 'sp_124606002'
         |when categoryId = '50008062' and (title rlike '薯片' or title rlike '爆米花' or title rlike '山药薄片' or title rlike '虾片' or title rlike '虾条'
         |or title rlike '雪米饼' or title rlike '锅巴' or title rlike '烤香馍片' or title rlike '拉面丸子'  or title rlike '仙贝') then 'sp_50008062'
         |when categoryId = '50010513' and (title rlike '薯片' or title rlike '爆米花' or title rlike '山药薄片' or title rlike '虾片' or title rlike '虾条'
         |or title rlike '雪米饼' or title rlike '锅巴' or title rlike '烤香馍片' or title rlike '拉面丸子'  or title rlike '仙贝') then 'sp_50010513'
         |when categoryId = '50012598' and (title rlike '薯片' or title rlike '爆米花' or title rlike '山药薄片' or title rlike '虾片' or title rlike '虾条'
         |or title rlike '雪米饼' or title rlike '锅巴' or title rlike '烤香馍片' or title rlike '拉面丸子'  or title rlike '仙贝') then 'sp_50012598'
         |when categoryId = '50010511' and (title rlike '薯片' or title rlike '爆米花' or title rlike '山药薄片' or title rlike '虾片' or title rlike '虾条'
         |or title rlike '雪米饼' or title rlike '锅巴' or title rlike '烤香馍片' or title rlike '拉面丸子'  or title rlike '仙贝') then 'sp_50010511'
         |when categoryId = '124298001' and (title rlike '薯片' or title rlike '爆米花' or title rlike '山药薄片' or title rlike '虾片' or title rlike '虾条'
         |or title rlike '雪米饼' or title rlike '锅巴' or title rlike '烤香馍片' or title rlike '拉面丸子'  or title rlike '仙贝') then 'sp_124298001'
         |
         |when categoryId = '124292002' and (title rlike '棒棒糖' or title rlike 'QQ糖' or (title rlike '水果' and title rlike '软糖') or title rlike '混合装糖果'
         |or title rlike '薄荷味糖' or title rlike '口香糖' or title rlike '水果糖' or title rlike '紫皮糖'  or title rlike '玉米软糖' or title rlike '什锦糖' or title rlike '软心球'
         |or title rlike '什锦喜庆糖' or title rlike '牛轧糖' or title rlike '酥心糖') then 'bbt_124292002'
         |when categoryId = '124296001' and (title rlike '棒棒糖' or title rlike 'QQ糖' or (title rlike '水果' and title rlike '软糖') or title rlike '混合装糖果'
         |or title rlike '薄荷味糖' or title rlike '口香糖' or title rlike '水果糖' or title rlike '紫皮糖'  or title rlike '玉米软糖' or title rlike '什锦糖' or title rlike '软心球'
         |or title rlike '什锦喜庆糖' or title rlike '牛轧糖' or title rlike '酥心糖') then 'bbt_124296001'
         |when categoryId = '124300001' and (title rlike '棒棒糖' or title rlike 'QQ糖' or (title rlike '水果' and title rlike '软糖') or title rlike '混合装糖果'
         |or title rlike '薄荷味糖' or title rlike '口香糖' or title rlike '水果糖' or title rlike '紫皮糖'  or title rlike '玉米软糖' or title rlike '什锦糖' or title rlike '软心球'
         |or title rlike '什锦喜庆糖' or title rlike '牛轧糖' or title rlike '酥心糖') then 'bbt_124300001'
         |when categoryId = '124302001' and (title rlike '棒棒糖' or title rlike 'QQ糖' or (title rlike '水果' and title rlike '软糖') or title rlike '混合装糖果'
         |or title rlike '薄荷味糖' or title rlike '口香糖' or title rlike '水果糖' or title rlike '紫皮糖'  or title rlike '玉米软糖' or title rlike '什锦糖' or title rlike '软心球'
         |or title rlike '什锦喜庆糖' or title rlike '牛轧糖' or title rlike '酥心糖') then 'bbt_124302001'
         |when categoryId = '124304001' and (title rlike '棒棒糖' or title rlike 'QQ糖' or (title rlike '水果' and title rlike '软糖') or title rlike '混合装糖果'
         |or title rlike '薄荷味糖' or title rlike '口香糖' or title rlike '水果糖' or title rlike '紫皮糖'  or title rlike '玉米软糖' or title rlike '什锦糖' or title rlike '软心球'
         |or title rlike '什锦喜庆糖' or title rlike '牛轧糖' or title rlike '酥心糖') then 'bbt_124304001'
         |when categoryId = '124306001' and (title rlike '棒棒糖' or title rlike 'QQ糖' or (title rlike '水果' and title rlike '软糖') or title rlike '混合装糖果'
         |or title rlike '薄荷味糖' or title rlike '口香糖' or title rlike '水果糖' or title rlike '紫皮糖'  or title rlike '玉米软糖' or title rlike '什锦糖' or title rlike '软心球'
         |or title rlike '什锦喜庆糖' or title rlike '牛轧糖' or title rlike '酥心糖') then 'bbt_124306001'
         |when categoryId = '124308001' and (title rlike '棒棒糖' or title rlike 'QQ糖' or (title rlike '水果' and title rlike '软糖') or title rlike '混合装糖果'
         |or title rlike '薄荷味糖' or title rlike '口香糖' or title rlike '水果糖' or title rlike '紫皮糖'  or title rlike '玉米软糖' or title rlike '什锦糖' or title rlike '软心球'
         |or title rlike '什锦喜庆糖' or title rlike '牛轧糖' or title rlike '酥心糖') then 'bbt_124308001'
         |when categoryId = '124310001' and (title rlike '棒棒糖' or title rlike 'QQ糖' or (title rlike '水果' and title rlike '软糖') or title rlike '混合装糖果'
         |or title rlike '薄荷味糖' or title rlike '口香糖' or title rlike '水果糖' or title rlike '紫皮糖'  or title rlike '玉米软糖' or title rlike '什锦糖' or title rlike '软心球'
         |or title rlike '什锦喜庆糖' or title rlike '牛轧糖' or title rlike '酥心糖') then 'bbt_124310001'
         |when categoryId = '124312001' and (title rlike '棒棒糖' or title rlike 'QQ糖' or (title rlike '水果' and title rlike '软糖') or title rlike '混合装糖果'
         |or title rlike '薄荷味糖' or title rlike '口香糖' or title rlike '水果糖' or title rlike '紫皮糖'  or title rlike '玉米软糖' or title rlike '什锦糖' or title rlike '软心球'
         |or title rlike '什锦喜庆糖' or title rlike '牛轧糖' or title rlike '酥心糖') then 'bbt_124312001'
         |when categoryId = '124604001' and (title rlike '棒棒糖' or title rlike 'QQ糖' or (title rlike '水果' and title rlike '软糖') or title rlike '混合装糖果'
         |or title rlike '薄荷味糖' or title rlike '口香糖' or title rlike '水果糖' or title rlike '紫皮糖'  or title rlike '玉米软糖' or title rlike '什锦糖' or title rlike '软心球'
         |or title rlike '什锦喜庆糖' or title rlike '牛轧糖' or title rlike '酥心糖') then 'bbt_124604001'
         |when categoryId = '124606001' and (title rlike '棒棒糖' or title rlike 'QQ糖' or (title rlike '水果' and title rlike '软糖') or title rlike '混合装糖果'
         |or title rlike '薄荷味糖' or title rlike '口香糖' or title rlike '水果糖' or title rlike '紫皮糖'  or title rlike '玉米软糖' or title rlike '什锦糖' or title rlike '软心球'
         |or title rlike '什锦喜庆糖' or title rlike '牛轧糖' or title rlike '酥心糖') then 'bbt_124606001'
         |when categoryId = '124606002' and (title rlike '棒棒糖' or title rlike 'QQ糖' or (title rlike '水果' and title rlike '软糖') or title rlike '混合装糖果'
         |or title rlike '薄荷味糖' or title rlike '口香糖' or title rlike '水果糖' or title rlike '紫皮糖'  or title rlike '玉米软糖' or title rlike '什锦糖' or title rlike '软心球'
         |or title rlike '什锦喜庆糖' or title rlike '牛轧糖' or title rlike '酥心糖') then 'bbt_124606002'
         |when categoryId = '50008062' and (title rlike '棒棒糖' or title rlike 'QQ糖' or (title rlike '水果' and title rlike '软糖') or title rlike '混合装糖果'
         |or title rlike '薄荷味糖' or title rlike '口香糖' or title rlike '水果糖' or title rlike '紫皮糖'  or title rlike '玉米软糖' or title rlike '什锦糖' or title rlike '软心球'
         |or title rlike '什锦喜庆糖' or title rlike '牛轧糖' or title rlike '酥心糖') then 'bbt_50008062'
         |when categoryId = '50010513' and (title rlike '棒棒糖' or title rlike 'QQ糖' or (title rlike '水果' and title rlike '软糖') or title rlike '混合装糖果'
         |or title rlike '薄荷味糖' or title rlike '口香糖' or title rlike '水果糖' or title rlike '紫皮糖'  or title rlike '玉米软糖' or title rlike '什锦糖' or title rlike '软心球'
         |or title rlike '什锦喜庆糖' or title rlike '牛轧糖' or title rlike '酥心糖') then 'bbt_50010513'
         |when categoryId = '50012598' and (title rlike '棒棒糖' or title rlike 'QQ糖' or (title rlike '水果' and title rlike '软糖') or title rlike '混合装糖果'
         |or title rlike '薄荷味糖' or title rlike '口香糖' or title rlike '水果糖' or title rlike '紫皮糖'  or title rlike '玉米软糖' or title rlike '什锦糖' or title rlike '软心球'
         |or title rlike '什锦喜庆糖' or title rlike '牛轧糖' or title rlike '酥心糖') then 'bbt_50012598'
         |when categoryId = '50010511' and (title rlike '棒棒糖' or title rlike 'QQ糖' or (title rlike '水果' and title rlike '软糖') or title rlike '混合装糖果'
         |or title rlike '薄荷味糖' or title rlike '口香糖' or title rlike '水果糖' or title rlike '紫皮糖'  or title rlike '玉米软糖' or title rlike '什锦糖' or title rlike '软心球'
         |or title rlike '什锦喜庆糖' or title rlike '牛轧糖' or title rlike '酥心糖') then 'bbt_50010511'
         |when categoryId = '124298001' and (title rlike '棒棒糖' or title rlike 'QQ糖' or (title rlike '水果' and title rlike '软糖') or title rlike '混合装糖果'
         |or title rlike '薄荷味糖' or title rlike '口香糖' or title rlike '水果糖' or title rlike '紫皮糖'  or title rlike '玉米软糖' or title rlike '什锦糖' or title rlike '软心球'
         |or title rlike '什锦喜庆糖' or title rlike '牛轧糖' or title rlike '酥心糖') then 'bbt_124298001'
         |else categoryId end
         |)
         |
         |--吸尘器和除螨仪
         |--when (categoryId = '124252007' or categoryId = '50008554') then (case
         |--when categoryId = '50008554' then (case
         |--when detailCategoryName rlike '立式' then (case when title rlike '桶' then 'tong_50008554' else 'scs_50008554' end)
         |--when detailCategoryName rlike '手持式' then 'scs_50008554'
         |--when detailCategoryName rlike '杆式' then 'scs_50008554'
         |--when detailCategoryName rlike '卧式' then 'ws_50008554'
         |--when detailCategoryName rlike '肩式' then 'scs_50008554'
         |--when detailCategoryName rlike '直立式' then 'scs_50008554'
         |--when detailCategoryName rlike '无线' then 'scs_50008554'
         |--else categoryId end) else 'cmy_124252007' end)
         |
         |----空调 565659551084 = 柜机圆柱单制冷立式
         |--when categoryId = '350401' then (case
         |--when detailCategoryName = '-1' then (case
         |--when title rlike '圆柱式' or title rlike '柜机圆柱' or title rlike '立柜' or title rlike '柜机'  then 'yzs_350401'
         |--when title rlike '壁挂' or title rlike '挂壁'  then 'bgs_350401'
         |--when title rlike '移动'   then 'yds_350401'
         |--else categoryId end) else (case
         |--when detailCategoryName rlike '圆柱' or detailCategoryName rlike '立柜式' or detailCategoryName rlike '柜机'  then 'yzs_350401'
         |--when detailCategoryName rlike '壁挂' or detailCategoryName rlike '挂壁' or detailCategoryName rlike '壁' then 'bgs_350401'
         |--when detailCategoryName rlike '移动'  then 'yds_350401'
         |--else (case
         |--when title rlike '圆柱式' or title rlike '柜机圆柱' or title rlike '立柜' or title rlike '柜机'  then 'yzs_350401'
         |--when title rlike '壁挂' or title rlike '挂壁'  then 'bgs_350401'
         |--when title rlike '移动'   then 'yds_350401'
         |--else categoryId end) end) end)
         |--头戴式耳机
         |when categoryId = '50024944' or categoryId = '50023280' or categoryId= '1205' then
         |(case
         |when categoryId = '50024944' and (title rlike '头戴式'or Base_Info rlike '头戴') then 'tds_50024944'
         |when categoryId = '50023280' and (title rlike '头戴式'or Base_Info rlike '头戴') then 'tds_50023280'
         |when categoryId = '1205' and (title rlike '头戴式'or Base_Info rlike '头戴') then 'tds_1205'
         |else categoryId end)
         |when categoryId = '50008882' then (case when title rlike '女士' then 'ns_50008882' else categoryId end)
         |when categoryId ='50348001'  then (case when title rlike '茶馆' or Base_Info rlike '茶馆'  then 'cg_50348001' else categoryId end)
         |--蒸汽眼罩
         |when categoryId ='50007006' or categoryId ='121468012' or categoryId ='121458012' or categoryId ='50011991' or categoryId ='121410013' then
         |(case
         |when categoryId ='50007006' and (title rlike '蒸汽' and title rlike '眼罩')  then 'zqyz_50007006'
         |when categoryId ='121468012' and (title rlike '蒸汽' and title rlike '眼罩')  then 'zqyz_121468012'
         |when categoryId ='121458012' and (title rlike '蒸汽' and title rlike '眼罩')  then 'zqyz_121458012'
         |when categoryId ='50011991' and (title rlike '蒸汽' and title rlike '眼罩')  then 'zqyz_50011991'
         |when categoryId ='121410013' and (title rlike '蒸汽' and title rlike '眼罩')  then 'zqyz_121410013'
         |else categoryId end)
         |when categoryId = '50006015' then (case when title rlike '吸奶器' then 'xnq_50006015' else categoryId end)
         |--乳酸菌
         |when categoryId = '50012392' or categoryId = '50012391' or categoryId = '50016427' then
         |(case
         |when categoryId = '50012392' and title rlike '乳酸菌' then 'rsj_50012392'
         |when categoryId = '50012391' and title rlike '乳酸菌' then 'rsj_50012391'
         |when categoryId = '50016427' and title rlike '乳酸菌' then 'rsj_50016427'
         |else categoryId end)
         |when categoryId = '50014781' then  (case when title rlike '手表' then 'sb_50014781' else categoryId end)
         |when categoryId = '50003558' then
         |(case
         |when title rlike '智能' or title rlike '自动'  or  title rlike '遥控' then categoryId
         |when title rlike '卫浴套餐' then 'wytc_50003558'
         |else categoryId end)
         |when categoryId = '50020032' then  (case when title rlike '智能' then 'zn_50020032' else categoryId end)
         |--牙刷
         |when categoryId = '50015564' or  categoryId = '50013976' then (
         |case
         |when categoryId = '50015564' and (title rlike '牙刷' or title rlike '刷头') then
         |(
         |case
         |when title rlike '振动' or title rlike '电动' or title rlike '震动'  then 'zdys_50015564'
         |when title not rlike '振动' and title not rlike '电动' and title not rlike '震动' then 'qt_50015564'
         |else categoryId end
         |)
         |when categoryId = '50013976' and  (title rlike '牙刷' or title rlike '刷头') then
         |(
         |case
         |when title rlike '振动' or title rlike '电动' or title rlike '震动'  then 'zdys_50013976'
         |when title not rlike '振动' and title not rlike '电动' and title not rlike '震动' then 'qt_50015564'
         |else categoryId end
         |)
         |else categoryId end
         |)
         |--钢丝球
         |when categoryId  ='127862002' then
         |(
         |case
         |when title rlike '清洗剂' or title rlike '钢丝球刷' or title rlike '洗锅刷' then 'qxj_127862002'
         |when title rlike '湿纸巾' or title rlike '湿巾' then 'szj_127862002'
         |else categoryId end)
         |
         |else categoryId end last_categoryId
         |from
         |casedata
      """.stripMargin)
    tmcatedf1.registerTempTable("tmcatedf1")
    //取出对应字段
    val tmcatedf = spark.sql(
      s"""
         |select
         |${a_aa_amainpackage.a_o2odata_deal.config.FieldsConfig.fields}
         |from
         |tmcatedf1
      """.stripMargin).dropDuplicates("good_id")
    //取出不需要匹配的分类 关联分类表
    val tmnocatedf2 = conandvar_res.where("categoryId  != '50010535' and  categoryId  != '123256002' and categoryId  != '50008059' and categoryId  != '123210006'" +
      "and categoryId  != '121422025' and categoryId  != '50008056' and categoryId  != '50016092' and categoryId  != '124302002'" +
      "and categoryId  != '124312003' and categoryId  != '124320001' and categoryId  != '124456021' and categoryId  != '124466010'" +
      "and categoryId  != '124468007' and categoryId  != '124478011' and categoryId  != '124492008' and categoryId  != '124512008'" +
      "and categoryId  != '126474004' and categoryId  != '50008055' and categoryId  != '124292002' and categoryId  != '124296001'" +
      "and categoryId  != '124300001' and categoryId  != '124302001' and categoryId  != '124304001' and categoryId  != '124306001'" +
      "and categoryId  != '124308001' and categoryId  != '124310001' and categoryId  != '124312001' and categoryId  != '124604001'" +
      "and categoryId  != '124606001' and categoryId  != '124606002' and categoryId  != '50008062' and categoryId  != '50010513'" +
      "and categoryId  != '50012598' and categoryId  != '50010511' and categoryId  != '124298001' and categoryId  != '1101' and  categoryId  != '124048001'  " +
      "and categoryId  != '124252007' and categoryId  != '50008554' and categoryId  != '350401' and categoryId  != '50024944' and categoryId  != '50023280' and categoryId  != '1205'" +
      "and categoryId  != '50008882' and categoryId  != '50348001' and categoryId  != '50007006' and categoryId  != '121468012' and  categoryId  != '121458012' " +
      "and categoryId  != '50011991' and categoryId  != '121410013' and categoryId  != '50006015' and categoryId  != '50012392' and  categoryId  != '50012391' " +
      "and categoryId  != '50016427' and categoryId  != '50014781' and categoryId  != '50003558' and categoryId  != '50020032' and  categoryId  != '50015564' " +
      "and categoryId  != '50013976' and categoryId  != '127862002'")
    tmnocatedf2.registerTempTable("tmnocatedf2_v1")
    spark.sql(
      """
        |select
        |*,
        |categoryId as last_categoryId
        |from
        |tmnocatedf2_v1
      """.stripMargin).registerTempTable("tmnocatedf2")
    val tmnocatedf = spark.sql(
      s"""
         |select
         |${a_aa_amainpackage.a_o2odata_deal.config.FieldsConfig.fields}
         |from
         |tmnocatedf2
      """.stripMargin).dropDuplicates("good_id")
    val tmdf = tmcatedf.union(tmnocatedf)
    tmdf.registerTempTable("tmdf")
    val catetable = spark.read.json(s"${category_obs_path}")
    catetable.registerTempTable("catetable")
    val catecompletedata = spark.sql(
      """
        |select
        |t1.*,
        |case when t2.last_categoryId is null then '10099' else t2.firstCategoryId end firstCategoryId,
        |case when t2.last_categoryId is null then '1009999' else t2.secondCategoryId end secondCategoryId,
        |case when t2.last_categoryId is null then '100999999' else t2.thirdCategoryId end thirdCategoryId,
        |case when t2.last_categoryId is null then '10099999999' else t2.fourthCategoryId end fourthCategoryId
        |from
        |tmdf t1
        |left join
        |catetable t2
        |on t1.last_categoryId = t2.last_categoryId
      """.stripMargin).dropDuplicates("good_id")
    catecompletedata
  }

  /**
    * 农产品关联
    * @return
    */
  def cate_pro(spark:SparkSession, cate_res:DataFrame):DataFrame = {
    val pro_table = spark.read.json(s"${products_obs_path}")
    pro_table.registerTempTable("pro_table")

    cate_res.registerTempTable("cate_res")

    val cate_resdata =  spark.sql(
      """
        |select
        |t1.*,
        |case when t2.thirdCategoryId is null then '1000' else t2.products end as products
        |from
        |cate_res t1 left join
        |pro_table t2
        |on t1.thirdCategoryId = t2.thirdCategoryId
      """.stripMargin).dropDuplicates("good_id")
    return cate_resdata
  }

  /**
    * 地址关联
    */
  def addrssjoin(spark:SparkSession,cate_resdata:DataFrame):DataFrame = {
    cate_resdata.registerTempTable("cate_resdata")
    val address_table = spark.read.json(s"${address_obs_path}")
    address_table.registerTempTable("address_table")
    cate_resdata.registerTempTable("cate_resdata")
    val address_res =  spark.sql(
      """
        |select
        |t1.*,
        |case when t2.shopId is null then '0' else t2.town end as  town,
        |case when t2.shopId is null then '0' else t2.address  end as  address,
        |case when t2.shopId is null then '0' else t2.administrative_region  end as  administrative_region,
        |case when t2.shopId is null then '0' else t2.city  end as  city,
        |case when t2.shopId is null then '0' else t2.city_grade  end as  city_grade,
        |case when t2.shopId is null then '0' else t2.city_origin  end as  city_origin,
        |case when t2.shopId is null then '0' else t2.district  end as  district,
        |case when t2.shopId is null then '0' else t2.district_origin  end as  district_origin,
        |case when t2.shopId is null then '0' else t2.economic_division  end as  economic_division,
        |case when t2.shopId is null then '0' else t2.if_district  end as  if_district,
        |case when t2.shopId is null then '0' else t2.if_city  end as  if_city,
        |case when t2.shopId is null then '0' else t2.if_state_level_new_areas  end as  if_state_level_new_areas,
        |case when t2.shopId is null then '0' else t2.name  end as  name,
        |case when t2.shopId is null then '0' else t2.poor_counties  end as  poor_counties,
        |case when t2.shopId is null then '0' else t2.province  end as  province,
        |case when t2.shopId is null then '0' else t2.regional_ID  end as  regional_ID,
        |case when t2.shopId is null then '0' else t2.rural_demonstration_counties  end as  rural_demonstration_counties,
        |case when t2.shopId is null then '0' else t2.rural_ecommerce  end as  rural_ecommerce,
        |case when t2.shopId is null then '0' else t2.the_belt_and_road_city  end as  the_belt_and_road_city,
        |case when t2.shopId is null then '0' else t2.the_yangtze_river_economic_zone_city  end as  the_yangtze_river_economic_zone_city,
        |case when t2.shopId is null then '0' else t2.the_belt_and_road_province  end as  the_belt_and_road_province,
        |case when t2.shopId is null then '0' else t2.the_yangtze_river_economic_zone_province  end as  the_yangtze_river_economic_zone_province,
        |case when t2.shopId is null then '0' else t2.urban_agglomerations  end as  urban_agglomerations,
        |case when t2.shopId is null then '-1' else t2.longitude end as  longitude,
        |case when t2.shopId is null then '-1' else t2.latitude end as latitude,
        |case when t2.shopId is null then '-1' else t2.aedzId end as aedzId,
        |case when t2.shopId is null then '-1' else t2.registration_institution end as registration_institution
        |from
        |cate_resdata t1
        |left join
        |address_table t2
        |on t1.shopId  =t2.shopId
      """.stripMargin).dropDuplicates("good_id")
    return address_res
  }

  def swb_productsjoin(spark:SparkSession,addrssjoined:DataFrame):DataFrame = {

    val swbprotable = spark.read.json(s"${swb_products_path}")
    swbprotable.registerTempTable("swbprotable")

    addrssjoined.registerTempTable("addrssjoined")

    /**
      * .withColumn("swb_firstcategoryId",lit("2003"))
      * .withColumn("swb_firstcategoryName",lit("茶叶"))
      * .withColumn("swb_products",lit("1101"))
      */
    val data = spark.sql(
      """
        |select
        |*,
        |case when rootCategoryId  ='124458005' then '1101' else swb_products end as swb_productss,
        |case when rootCategoryId  ='124458005' then '2003' else swb_firstcategoryId end as swb_firstcategoryIds,
        |case when rootCategoryId  ='124458005' then '茶叶' else swb_firstcategoryName end as swb_firstcategoryNames
        |from
        |(select
        |t1.*,
        |case when t2.categoryId is null then '-1' else t2.swb_firstCategoryId end as swb_firstcategoryId,
        |case when t2.categoryId is null then '-1' else t2.swb_firstCategoryName end as swb_firstcategoryName,
        |case when t2.categoryId is null then '1100' else t2.swb_products end as swb_products
        |from
        |addrssjoined t1
        |left join
        |swbprotable t2
        |on t1.categoryId = t2.categoryId)
      """.stripMargin).drop("swb_products","swb_firstcategoryId","swb_firstcategoryName")
      .withColumnRenamed("swb_productss","swb_products")
      .withColumnRenamed("swb_firstcategoryIds","swb_firstcategoryId")
      .withColumnRenamed("swb_firstcategoryNames","swb_firstcategoryName")
    return data
  }


}
